package com.zzyy.study.batch;

import org.springframework.transaction.annotation.Transactional;

import java.sql.*;
import java.util.ArrayList;
import java.util.List;

/**
 * @auther zzyy
 * @create 2024-09-08 11:04
 */
public class Update1000W
{

    public static void main(String[] args) throws SQLException, ClassNotFoundException
    {
        long startTime = System.nanoTime();
        myBatchUpdate();
        long endTime = System.nanoTime();
        System.out.println("----costTime: "+(endTime - startTime)/1000000000.0 +" 秒");
    }


    private static void myBatchUpdate() throws ClassNotFoundException, SQLException
    {
        long minPKId = 0;

        Class.forName("com.mysql.cj.jdbc.Driver");
        String jdbcUrl = "jdbc:mysql://localhost:3306/bigdata?useUnicode=true&characterEncoding=utf8&zeroDateTimeBehavior=convertToNull&useSSL=true&serverTimezone=GMT%2B8";
        String username = "root";
        String password = "123456";

        Connection connection = DriverManager.getConnection(jdbcUrl, username, password);
        PreparedStatement preparedStatement = null;
        PreparedStatement preparedStatement2 = null;
        PreparedStatement preparedStatement3 = null;

        try
        {
            //1 创建PreparedStatement对象，并设置为批处理模式
            preparedStatement = connection.prepareStatement("SELECT MIN(id) as id FROM emp");
            //2
            ResultSet resultSet = preparedStatement.executeQuery();
            if(resultSet.next())
            {
                minPKId = resultSet.getInt("id");
                //System.out.println("emp表最小主键id：" + minPKId);
            }

            List<Integer> idList = new ArrayList<>();
            String sql2 = "SELECT id FROM emp WHERE id>="+minPKId+" ORDER BY id LIMIT 1000";
            String sql3 = "UPDATE emp SET sal=sal+1 WHERE id IN (";

            //循环批量更新数据
            while(true)
            {
                //查询出1000条需要更新的记录的id，注意查询条件(id>=#{minPKId})，
                // 这个查询会走主键索引，查询会特别快，毫秒级别
                //System.out.println(sql2);
                preparedStatement2 = connection.prepareStatement(sql2);
                ResultSet resultSet2 = preparedStatement2.executeQuery();

                if(resultSet2.isBeforeFirst())
                {
                    while(resultSet2.next())
                    {
                        idList.add(resultSet2.getInt("id"));
                        sql3 = sql3 + resultSet2.getString("id") + ",";
                    }
                    sql3 =  sql3.substring(0,(sql3.length()-1));//去掉最后一个,号
                    sql3 =  sql3 + ")";//尾部最后加上括号)
                    //System.out.println(sql3);
                    preparedStatement3 = connection.prepareStatement(sql3);
                    preparedStatement3.executeUpdate();

                    //minId = idList最后一条记录的id
                    minPKId = idList.get(idList.size()-1);
                    minPKId = minPKId + 1;
                    //System.out.println("*****minPKId: "+minPKId);
                    sql2 = "SELECT id FROM emp WHERE id>="+minPKId+" ORDER BY id LIMIT 1000";
                    sql3 = "UPDATE emp SET sal=sal+1 WHERE id IN (";
                }else{
                    break;
                }
            }
        } catch (SQLException e) {
            e.printStackTrace();
            // 发生异常时回滚事务
            if (connection != null) {
                connection.rollback();
            }
        } finally {
            // 关闭PreparedStatement和Connection
            if (preparedStatement != null) {
                preparedStatement.close();
            }
            if (connection != null) {
                connection.close();
            }
        }
    }
}
